
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_whole_trunc_base_dt
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,substr_route_type as route_type
    ,branch_in_total_all_time
    ,main_total_all_time
    ,branch_out_total_all_time
    ,dt
from (
    select
         *
        ,substr_route_type as route_type
        ,1 as rn 
    from jms_dm.dm_route_whole_substr_base_dt
    where dt = '{{ execution_date | cst_ds }}'
    and substr_route_type in ('网点-集散','中心-集散')
    union all
    select
         *
    from (
        select *
            ,substr_route_type as route_type
            ,row_number() over(partition by in_from_name
                                           ,in_collect_code       
                                           ,extra_in_collect_code 
                                           ,in_to_code            
                                           ,e1_end_code           
                                           ,e2_end_code           
                                           ,e3_end_code           
                                           ,e4_end_code           
                                           ,e5_end_code           
                                           ,e6_end_code           
                                           ,out_collect_code      
                                           ,extra_out_collect_code
                                           ,out_to_code       
                                           ,coalesce(
                                            if(length(in_network_line_name   )>0,in_network_line_name ,null)
                                           ,if(length(in_collect_line_name   )>0,in_collect_line_name ,null)
                                           ,if(length(extra_in_line_name     )>0,extra_in_line_name   ,null)
                                           ,if(length(e1_line_code           )>0,e1_line_code         ,null)
                                           ,if(length(e2_line_code           )>0,e2_line_code         ,null)
                                           ,if(length(e3_line_code           )>0,e3_line_code         ,null)
                                           ,if(length(e4_line_code           )>0,e4_line_code         ,null)
                                           ,if(length(e5_line_code           )>0,e5_line_code         ,null)
                                           ,if(length(e6_line_code           )>0,e6_line_code         ,null)
                                           ,if(length(out_collect_line_name  )>0,out_collect_line_name,null)
                                           ,if(length(extra_out_line_name    )>0,extra_out_line_name  ,null)
                                           ,if(length(out_network_line_name  )>0,out_network_line_name,null))
                                  order by search_type asc
                                          ,total_time_use asc
                                          ,in_edge_planned_departure desc
                                          ,in_edge2_planned_departure desc
                                          ,extra_in_planned_departure desc
                                          ,e1_planned_departure desc
                                          ,e2_planned_departure desc
                                          ,e3_planned_departure desc
                                          ,e4_planned_departure desc
                                          ,e5_planned_departure desc
                                          ,e6_planned_departure desc
                                          ,out_edge_planned_departure desc
                                          ,extra_out_planned_departure desc
                                          ,out_edge2_planned_departure desc
                               ) as rn 
        from jms_dm.dm_route_whole_substr_base_dt
        where dt = '{{ execution_date | cst_ds }}'
        and substr_route_type in ('中心-中心','网点-中心','中心-网点')
    ) a where a.rn = 1
) a
distribute by dt,pmod(hash(rand()),50);